﻿/**
 * @copyright Copyright 2009 Structured Solutions
 * @license http://www.codeplex.com/ShippingAgent/license Microsoft Public License (Ms-PL)
 * @version 3.6
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Web.Services;
using System.Web.Services.Protocols;
using CommerceBuilder.Common;
using CommerceBuilder.Orders;
using CommerceBuilder.Payments;
using CommerceBuilder.Products;
using CommerceBuilder.Shipping;
using CommerceBuilder.Users;
using CommerceBuilder.Utility;
using StructuredSolutions.ShippingAgent.Properties;

namespace StructuredSolutions.ShippingAgent
{
    [WebService(Namespace = "http://www.codeplex.com/ShippingAgent",
        Description = "Shipping Agent for AbleCommerce 7.0.2 and 7.0.3")]
    public class ShippingAgentImpl : ShippingAgentService
    {
        public const string VERSION = "3.6.0";

        #region WebMethod Implementations

        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve the features supported by this implementation of Shipping Agent.")]
        public override Features GetFeatures()
        {
            try
            {
                AuthenticateUser();

                return new Features { SupportsNewAndUpdatedOrders = true };
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve features.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Return orders created or updated since pageKey. If pageKey is null or an empty string, 
        /// then this implmentation returns all the orders from today.
        /// </summary>
        /// <param name="orderStatusList">A list of order statuses to include.</param>
        /// <param name="paymentStatusList">A list of order payment statuses to include.</param>
        /// <param name="pageKey">A key used to divide the results into pages. This implementation stores
        /// the last order number of the previous results in the pageKey. May be null or an empty string
        /// if this is the first time this method is called.</param>
        /// <param name="maxCount">The maximum number of orders to return.</param>
        /// <returns>Up to maxCount OrderResults, or an empty list or null if there are no new orders.</returns>
        /// <remarks>
        /// <para>If maxCount orders are returned, the Shipper client will call this method again with the 
        /// same ranges and with pageKey set to the value returned in the previous result. In this
        /// implementation, the pageKey is the last order number which is used to calculate an appropriate 
        /// new startOrderNumber (for example, startOrderNumber = lastOrderNumber + 1).</para>
        /// <para>If the order status is not in the orderStatusList, or if the order
        /// payment status is not in the paymentStatusList, the order is ignored.</para>
        /// </remarks>
        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve orders created or updated since pageKey.")]
        public override OrderPage GetNewAndUpdatedOrders(string orderStatusList, string paymentStatusList, string pageKey, int maxCount)
        {
            try
            {
                AuthenticateUser();

                return GetOrdersFromStore(orderStatusList, paymentStatusList, pageKey, maxCount, true);
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve new and updated orders.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Return orders created since pageKey. If pageKey is null or an empty string, 
        /// then this implmentation returns all the orders from today.
        /// </summary>
        /// <param name="orderStatusList">A list of order statuses to include.</param>
        /// <param name="paymentStatusList">A list of order payment statuses to include.</param>
        /// <param name="pageKey">A key used to divide the results into pages. This implementation stores
        /// the last order number of the previous results in the pageKey. May be null or an empty string
        /// if this is the first time this method is called.</param>
        /// <param name="maxCount">The maximum number of orders to return.</param>
        /// <returns>Up to maxCount OrderResults, or an empty list or null if there are no new orders.</returns>
        /// <remarks>
        /// <para>If maxCount orders are returned, the Shipper client will call this method again with the 
        /// same ranges and with pageKey set to the value returned in the previous result. In this
        /// implementation, the pageKey is the last order number which is used to calculate an appropriate 
        /// new startOrderNumber (for example, startOrderNumber = lastOrderNumber + 1).</para>
        /// <para>If the order status is not in the orderStatusList, or if the order
        /// payment status is not in the paymentStatusList, the order is ignored.</para>
        /// </remarks>
        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve orders created since pageKey.")]
        public override OrderPage GetNewOrders(string orderStatusList, string paymentStatusList, string pageKey, int maxCount)
        {
            try
            {
                AuthenticateUser();

                return GetOrdersFromStore(orderStatusList, paymentStatusList, pageKey, maxCount, false);
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve new orders.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Return one order.
        /// </summary>
        /// <param name="orderId">The ID of the order.</param>
        /// <returns>The OrderResult.</returns>
        /// <exception cref="SoapException">If the orderID is not valid (such as blank or null).</exception>
        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve one order.")]
        public override OrderResult GetOrder(string orderId)
        {
            try
            {
                AuthenticateUser();

                if (string.IsNullOrEmpty(orderId))
                    throw new ApplicationException("Missing order ID.");
                return GetOrderFromStore(orderId);
            }
            catch (ApplicationException ex)
            {
                var result = new OrderResult();
                result.Success = false;
                result.Message = ex.Message;
                return result;
            }
            catch (Exception ex)
            {
                var result = new OrderResult();
                result.Success = false;
                result.Message = ex.ToString();
                return result;
            }
        }

        /// <summary>
        /// Return all the orders that fall on and within the specified ranges.
        /// </summary>
        /// <param name="startOrderNumber">The starting order number; or null or an empty string to start at the first order number in the store.</param>
        /// <param name="endOrderNumber">The ending order number; or null or an empty string to end with the last order number in the store.</param>
        /// <param name="startOrderDate">The starting order date. If no start date is specified, start from the earliest order.</param>
        /// <param name="startOrderDateSpecified">True if startOrderDate is specified, false otherwise.</param>
        /// <param name="endOrderDate">The ending order date. If no end date is specified, end with the most recent order.</param>
        /// <param name="endOrderDateSpecified">True if endOrderDate is specified, false otherwise.</param>
        /// <param name="orderStatusList">A list of order statuses to include.</param>
        /// <param name="paymentStatusList">A list of order payment statuses to include.</param>
        /// <param name="pageKey">The last order number from the previous result; or null or an empty string to ignore this parameter.</param>
        /// <param name="maxCount">The maximum number of orders to return.</param>
        /// <returns>Up to maxCount Order's, sorted by order number; or an empty list or null 
        /// if there are no orders that fall on and within the specified ranges.</returns>
        /// <remarks><para>If maxCount orders are returned, the Shipper client will call this method again with the 
        /// same ranges and with lastOrderNumber set to the last order number in the previous result. In this
        /// case, use lastOrderNumber to calculate an appropriate new startOrderNumber (for example,
        /// startOrderNumber = lastOrderNumber + 1).</para>
        /// <para>If the order status is not in the orderStatusList, or if the order
        /// payment status is not in the paymentStatusList, the order is ignored.</para>
        /// </remarks>
        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Return the list of orders that fall on and within the specified ranges.")]
        public override OrderPage GetOrdersByRange(string startOrderNumber, string endOrderNumber, DateTime startOrderDate, bool startOrderDateSpecified, DateTime endOrderDate, bool endOrderDateSpecified, string orderStatusList, string paymentStatusList, string pageKey, int maxCount)
        {
            try
            {
                AuthenticateUser();

                var nullableStartDate = ShippingAgentHelper.ConvertWebServiceNullable(startOrderDate, startOrderDateSpecified);
                var nullableEndDate = ShippingAgentHelper.ConvertWebServiceNullable(endOrderDate, endOrderDateSpecified);
                return GetOrdersByRangeFromStore(startOrderNumber, endOrderNumber, nullableStartDate, nullableEndDate, orderStatusList, paymentStatusList, pageKey, maxCount);
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve orders by range.", SoapException.ServerFaultCode, ex);
            }
        }

        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve the list of all searchable order statuses.")]
        public override OrderStatus[] GetOrderStatuses()
        {
            try
            {
                AuthenticateUser();

                var statuses = new List<OrderStatus>();
                foreach (CommerceBuilder.Orders.OrderStatus cbOrderStatus in OrderStatusDataSource.LoadForStore())
                {
                    statuses.Add(
                        new OrderStatus
                            {
                                Name = cbOrderStatus.Name, 
                                OrderStatusId = cbOrderStatus.OrderStatusId.ToString()
                            });
                }
                return statuses.ToArray();
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve order statuses.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Return one specific package.
        /// </summary>
        /// <param name="orderId">Ignored in this implementation.</param>
        /// <param name="packageId">The ID of the package.</param>
        /// <returns>A PackageResult.</returns>
        /// <exception cref="ApplicationException">If the orderId or packageId is not valid (such as blank or null).</exception>
        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve one package.")]
        public override PackageResult GetPackage(string orderId, string packageId)
        {
            try
            {
                AuthenticateUser();

                if (string.IsNullOrEmpty(packageId))
                    throw new ApplicationException("Invalid package ID.");
                return GetPackageFromStore(packageId);
            }
            catch (ApplicationException ex)
            {
                var result = new PackageResult();
                result.Success = false;
                result.Message = ex.Message;
                return result;
            }
            catch (Exception ex)
            {
                var result = new PackageResult();
                result.Success = false;
                result.Message = ex.ToString();
                return result;
            }
        }

        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve the list of all searchable order payment statuses.")]
        public override PaymentStatus[] GetPaymentStatuses()
        {
            try
            {
                AuthenticateUser();

                var statuses = new List<PaymentStatus>();
                foreach (OrderPaymentStatus cbPaymentStatus in Enum.GetValues(typeof(OrderPaymentStatus)))
                {
                    statuses.Add(
                        new PaymentStatus
                            {
                                Name = ShippingAgentHelper.SplitCompoundName(cbPaymentStatus.ToString()),
                                PaymentStatusId = ((int) cbPaymentStatus).ToString()
                            });
                }
                return statuses.ToArray();
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve payment statuses.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Returns the complete list of shipping rate providers offered by the store.
        /// </summary>
        /// <returns>The list of RateProviders's.</returns>
        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve the list of shipping rate providers.")]
        public override RateProvider[] GetRateProviders()
        {
            try
            {
                AuthenticateUser();

                return GetRateProvidersFromStore().ToArray();
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve rate providers.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Returns the complete list of tracking providers supported by the store. The
        /// Shipper client may assign any tracking provider to a package, regardless of
        /// how the shipping cost was initially calculated.
        /// </summary>
        /// <returns>The list of TrackingProvider's.</returns>
        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve the list of package tracking providers.")]
        public override TrackingProvider[] GetTrackingProviders()
        {
            try
            {
                AuthenticateUser();

                return GetTrackingProvidersFromStore().ToArray();
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve tracking providers.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Return the ShippingAgent.wsdl implemented by this ShippingAgent.
        /// </summary>
        /// <returns>A string value that represents the version using the format
        /// Major.Minor.Revision. For example, "3.0.0".</returns>
        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Retrieve the WSDL interface version implemented by this Shipping Agent.")]
        public override string GetVersion()
        {
            try
            {
                AuthenticateUser();

                return VERSION;
            }
            catch (ApplicationException ex)
            {
                throw new DetailedSoapException(ex.Message, SoapException.ServerFaultCode, ex);
            }
            catch (Exception ex)
            {
                throw new DetailedSoapException("Cannot retrieve version.", SoapException.ServerFaultCode, ex);
            }
        }

        /// <summary>
        /// Record package information.
        /// </summary>
        /// <param name="update">The package information to record.</param>
        /// <returns>If the update is successful, the UpdateResult contains a fresh
        /// copy of the Order that contains the package. If the update is not
        /// successful, the UpdateResult contains the reason why the update failed.</returns>
        /// <remarks>The Shipper client may not include all Update elements. null
        /// elements should be ignored. If the ShipDate is included, then the package
        /// should be marked shipped.</remarks>
        [SoapHeader("CredentialsValue", Direction = SoapHeaderDirection.In)]
        [WebMethod(Description = "Record package information.")]
        public override UpdateResult UpdatePackage(Update update)
        {
            try
            {
                AuthenticateUser();

                if (update == null)
                    return null;
                if (string.IsNullOrEmpty(update.OrderId))
                    throw new ApplicationException("Invalid order ID.");
                if (string.IsNullOrEmpty(update.PackageId))
                    throw new ApplicationException("Invalid package ID.");
                return UpdatePackageInStore(update);
            }
            catch (ApplicationException ex)
            {
                var result = new UpdateResult();
                result.Success = false;
                result.Message = ex.Message;
                return result;
            }
            catch (Exception ex)
            {
                var result = new UpdateResult();
                result.Success = false;
                result.Message = ex.ToString();
                return result;
            }
        }

        #endregion

        #region Abstract Method Implementation

        protected static OrderResult GetOrderFromStore(string orderId)
        {
            int cbOrderId;
            if (!int.TryParse(orderId, out cbOrderId))
                throw new ApplicationException("Invalid order ID.");

            var cbOrder = OrderDataSource.Load(cbOrderId, false);
            if (cbOrder == null)
                throw new ApplicationException("Invalid order ID.");

            return BuildOrderResult(cbOrder);
        }

        protected static OrderPage GetOrdersByRangeFromStore(string startOrderNumber, string endOrderNumber, DateTime? startOrderDate, DateTime? endOrderDate, string orderStatusList, string paymentStatusList, string pageKey, int maxCount)
        {
            var query = BuildOrderQuery(startOrderNumber, endOrderNumber, startOrderDate, endOrderDate, orderStatusList, paymentStatusList, pageKey);

            int total;
            using (var command = Token.Instance.Database.GetSqlStringCommand("SELECT COUNT(*) FROM " + query.Sql))
            {
                foreach (var parameter in query.Parameters)
                {
                    Token.Instance.Database.AddInParameter(command, parameter.Name, parameter.DbType, parameter.Value);
                }

                total = (int)Token.Instance.Database.ExecuteScalar(command);
            }

            if (total == 0) return null;

            var orderIds = new List<string>();
            var sql = "SELECT TOP " + maxCount + " OrderId FROM " + query.Sql + " ORDER BY OrderId";
            using (var command = Token.Instance.Database.GetSqlStringCommand(sql))
            {
                foreach (var parameter in query.Parameters)
                {
                    Token.Instance.Database.AddInParameter(command, parameter.Name, parameter.DbType, parameter.Value);
                }

                using (var reader = Token.Instance.Database.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        orderIds.Add(ShippingAgentHelper.ConvertToString(reader["OrderId"]));
                    }
                }
            }

            var result = new OrderPage();
            result.Key = orderIds[orderIds.Count - 1];
            result.Orders = orderIds.ToArray();
            result.Remaining = orderIds.Count < maxCount ? 0 : total - orderIds.Count;
            return result;
        }

        protected static OrderPage GetOrdersFromStore(string orderStatusList, string paymentStatusList, string pageKey, int maxCount, bool getNewAndUpdated)
        {
            var query = BuildOrderQuery(orderStatusList, paymentStatusList, pageKey, getNewAndUpdated);

            int total;
            using (var command = Token.Instance.Database.GetSqlStringCommand("SELECT COUNT(*) FROM " + query.Sql))
            {
                foreach (var parameter in query.Parameters)
                {
                    Token.Instance.Database.AddInParameter(command, parameter.Name, parameter.DbType, parameter.Value);
                }

                total = (int)Token.Instance.Database.ExecuteScalar(command);
            }

            if (total == 0) return null;

            var orderIds = new List<string>();
            var lastUpdated = DateTime.UtcNow;
            string sql;
            if (getNewAndUpdated)
            {
                sql = "SELECT TOP " + maxCount + " OrderId, LastUpdated FROM " + query.Sql + " ORDER BY LastUpdated";
            }
            else
            {
                sql = "SELECT TOP " + maxCount + " OrderId, LastUpdated FROM " + query.Sql + " ORDER BY OrderId";
            }
            using (var command = Token.Instance.Database.GetSqlStringCommand(sql))
            {
                foreach (var parameter in query.Parameters)
                {
                    Token.Instance.Database.AddInParameter(command, parameter.Name, parameter.DbType, parameter.Value);
                }

                using (var reader = Token.Instance.Database.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        orderIds.Add(ShippingAgentHelper.ConvertToString(reader["OrderId"]));
                        if (!reader.IsDBNull(1)) lastUpdated = reader.GetDateTime(1);
                    }
                }
            }

            var result = new OrderPage();
            result.Key = getNewAndUpdated ? lastUpdated.ToBinary().ToString() : orderIds[orderIds.Count - 1];
            result.Orders = orderIds.ToArray();
            result.Remaining = orderIds.Count < maxCount ? 0 : total - orderIds.Count;
            return result;
        }

        /// <summary>
        /// Return one shipment from the store.
        /// </summary>
        /// <param name="packageId">In this implementation, the packageId is a combination
        /// of OrderShipmentId and TrackingNumberId. One Package is generated for each combination.
        /// TrackingNumberId can be blank. Valid packageId formats: "1,1", "1,", and "1".</param>
        /// <returns>The Shipment corresponding to the AC OrderShipment and TrackingNumber.</returns>
        protected static PackageResult GetPackageFromStore(string packageId)
        {
            var packageIds = packageId.Split(',');
            int cbOrderShipmentId;
            if (!int.TryParse(packageIds[0], out cbOrderShipmentId))
                throw new ApplicationException("Invalid order shipment ID.");

            var cbOrderShipment = OrderShipmentDataSource.Load(cbOrderShipmentId, false);
            if (cbOrderShipment == null)
                throw new ApplicationException("Invalid package ID.");

            if (packageIds.Length > 1 && !string.IsNullOrEmpty(packageIds[1]))
            {
                int cbTrackingNumberId;
                if (!int.TryParse(packageIds[1], out cbTrackingNumberId))
                    throw new ApplicationException("Invalid tracking number ID.");

                foreach (TrackingNumber cbTrackingNumber in cbOrderShipment.TrackingNumbers)
                {
                    if (cbTrackingNumber.TrackingNumberId == cbTrackingNumberId)
                    {
                        return BuildPackageResult(cbOrderShipment, cbTrackingNumber);
                    }
                }
                throw new ApplicationException("Invalid tracking number ID.");
            }
            return BuildPackageResult(cbOrderShipment, null);
        }

        protected static List<RateProvider> GetRateProvidersFromStore()
        {
            var dictionary = new Dictionary<string, List<string>>();
            foreach (ShipMethod shipMethod in ShipMethodDataSource.LoadForStore())
            {
                var name = BuildRateProviderName(shipMethod);
                if (string.IsNullOrEmpty(name)) continue;

                if (!dictionary.ContainsKey(name))
                {
                    dictionary.Add(name, new List<string>());
                }

                var rateOptions = dictionary[name];
                var rateOptionName = ShippingAgentHelper.StringOrNull(shipMethod.Name);
                if (!rateOptions.Contains(rateOptionName))
                {
                    rateOptions.Add(rateOptionName);
                }
            }

            var rateProviders = new List<RateProvider>();
            foreach (var pair in dictionary)
            {
                var rateProvider = new RateProvider();
                rateProvider.Name = pair.Key;
                rateProvider.RateOptions = pair.Value.ToArray();
                rateProviders.Add(rateProvider);
            }

            return rateProviders;
        }

        protected static List<TrackingProvider> GetTrackingProvidersFromStore()
        {
            var trackingProviders = new List<TrackingProvider>();
            foreach (ShipGateway shipGateway in ShipGatewayDataSource.LoadForStore())
            {
                var provider = new TrackingProvider();
                provider.Name = shipGateway.Name;
                provider.TrackingProviderId = shipGateway.ShipGatewayId.ToString();
                trackingProviders.Add(provider);
            }
            return trackingProviders;
        }

        protected static UpdateResult UpdatePackageInStore(Update update)
        {
            var packageIds = update.PackageId.Split(',');
            int cbOrderShipmentId;
            if (!int.TryParse(packageIds[0], out cbOrderShipmentId))
                throw new ApplicationException("Invalid order shipment ID.");

            var cbOrderShipment = OrderShipmentDataSource.Load(cbOrderShipmentId, false);
            if (cbOrderShipment == null)
                throw new ApplicationException("Invalid package ID.");

            if (packageIds.Length > 1 && !string.IsNullOrEmpty(packageIds[1]))
            {
                int cbTrackingNumberId;
                if (!int.TryParse(packageIds[1], out cbTrackingNumberId))
                    throw new ApplicationException("Invalid tracking number ID.");

                foreach (TrackingNumber cbTrackingNumber in cbOrderShipment.TrackingNumbers)
                {
                    if (cbTrackingNumber.TrackingNumberId == cbTrackingNumberId)
                    {
                        return BuildUpdateResult(update, cbOrderShipment, cbTrackingNumber);
                    }
                }
                throw new ApplicationException("Invalid tracking number ID.");
            }
            return BuildUpdateResult(update, cbOrderShipment, null);
        }

        #endregion

        /// <summary>
        /// Authorize the request using the username and password in the Basic Authentication header.
        /// </summary>
        private void AuthenticateUser()
        {
            if (CredentialsValue == null 
                || string.IsNullOrEmpty(CredentialsValue.Username) 
                || string.IsNullOrEmpty(CredentialsValue.Password))
            {
                throw new ApplicationException("You must supply a valid username and password.");
            }

            // 
            // Validate the user credentials 
            // 
            if (!CommerceBuilder.Users.User.Login(CredentialsValue.Username, CredentialsValue.Password) 
                || !UserDataSource.LoadForUserName(CredentialsValue.Username).IsAdmin)
            {
                throw new ApplicationException("The username and password are not authorized to access Shipping Agent.");
            }
        }

        private static bool AddressesAreEqual(OrderShipment acShipment, ShippingAddress shippingAddress)
        {
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToAddress1, shippingAddress.Line1, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToAddress2, shippingAddress.Line2, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToCity, shippingAddress.City, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToCompany, shippingAddress.Company, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToCountryCode, shippingAddress.Country, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToEmail, shippingAddress.Email, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToFax, shippingAddress.Fax, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToFirstName, shippingAddress.FirstName, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToLastName, shippingAddress.LastName, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToPhone, shippingAddress.Phone, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToPostalCode, shippingAddress.PostalCode, StringComparison.CurrentCulture)) return false;
            if (!ShippingAgentHelper.StringsAreEqual(acShipment.ShipToProvince, shippingAddress.Region, StringComparison.CurrentCulture)) return false;
            if (acShipment.ShipToResidence != shippingAddress.Residential) return false;
            return true;
        }

        private static Address BuildAddress(CommerceBuilder.Orders.Order cbOrder)
        {
            var address = new Address();
            address.AddressId = cbOrder.OrderId.ToString();
            address.City = ShippingAgentHelper.StringOrNull(cbOrder.BillToCity);
            address.Company = ShippingAgentHelper.StringOrNull(cbOrder.BillToCompany);
            address.Country = ShippingAgentHelper.StringOrNull(cbOrder.BillToCountryCode);
            address.Email = ShippingAgentHelper.StringOrNull(cbOrder.BillToEmail);
            address.Fax = ShippingAgentHelper.StringOrNull(cbOrder.BillToFax);
            address.FirstName = ShippingAgentHelper.StringOrNull(cbOrder.BillToFirstName);
            address.LastName = ShippingAgentHelper.StringOrNull(cbOrder.BillToLastName);
            address.Line1 = ShippingAgentHelper.StringOrNull(cbOrder.BillToAddress1);
            address.Line2 = ShippingAgentHelper.StringOrNull(cbOrder.BillToAddress2);
            address.Phone = ShippingAgentHelper.StringOrNull(cbOrder.BillToPhone);
            address.PostalCode = ShippingAgentHelper.StringOrNull(cbOrder.BillToPostalCode);
            address.Region = ShippingAgentHelper.StringOrNull(cbOrder.BillToProvince);
            return address;
        }

        private static Address BuildAddress(Warehouse cbWarehouse)
        {
            if (cbWarehouse == null) return null;

            var warehouse = new Address();
            warehouse.AddressId = cbWarehouse.WarehouseId.ToString();
            warehouse.City = ShippingAgentHelper.StringOrNull(cbWarehouse.City);
            warehouse.Company = ShippingAgentHelper.StringOrNull(cbWarehouse.Name);
            warehouse.Country = ShippingAgentHelper.StringOrNull(cbWarehouse.CountryCode);
            warehouse.Email = ShippingAgentHelper.StringOrNull(cbWarehouse.Email);
            warehouse.Fax = ShippingAgentHelper.StringOrNull(cbWarehouse.Fax);
            warehouse.Line1 = ShippingAgentHelper.StringOrNull(cbWarehouse.Address1);
            warehouse.Line2 = ShippingAgentHelper.StringOrNull(cbWarehouse.Address2);
            warehouse.Phone = ShippingAgentHelper.StringOrNull(cbWarehouse.Phone);
            warehouse.PostalCode = ShippingAgentHelper.StringOrNull(cbWarehouse.PostalCode);
            warehouse.Region = ShippingAgentHelper.StringOrNull(cbWarehouse.Province);

            return warehouse;
        }

        private static NamedAmount[] BuildCharges(OrderItemCollection cbOrderItems)
        {
            var charges = new NamedAmountList();
            foreach (OrderItem orderItem in cbOrderItems)
            {
                switch (orderItem.OrderItemType)
                {
                    case OrderItemType.Handling:
                    case OrderItemType.Shipping:
                        charges["Shipping"].Amount += orderItem.ExtendedPrice.ToDecimal(null);
                        break;
                    case OrderItemType.Tax:
                        charges["Taxes"].Amount += orderItem.ExtendedPrice.ToDecimal(null);
                        break;
                    case OrderItemType.Charge:
                    case OrderItemType.Credit:
                        charges["Other Charges"].Amount += orderItem.ExtendedPrice.ToDecimal(null);
                        break;
                }
            }
            return charges.Count > 0 ? charges.ToArray() : null;
        }

        private static Customer BuildCustomer(User user)
        {
            if (user == null) return null;

            var customer = new Customer();
            customer.CustomerId = user.UserId.ToString();
            customer.CustomerUrl = BuildCustomerUrl("Members/MyAccount.aspx", "");
            customer.Email = user.Email;
            customer.MerchantUrl = BuildMerchantUrl("Admin/People/Users/EditUser.aspx", "UserId=" + user.UserId);
            customer.UserName = user.UserName;
            if (user.PrimaryAddress != null)
            {
                customer.FirstName = user.PrimaryAddress.FirstName;
                customer.LastName = user.PrimaryAddress.LastName;
            }
            return customer;
        }

        private static string BuildCustomerUrl(string path, string query)
        {
            try
            {
                var uri = new UriBuilder(Token.Instance.Store.StoreUrl);
                uri.Path += path;
                uri.Query = query;
                return uri.Uri.ToString();
            }
            catch
            {
                return string.Empty;
            }
        }

        private static decimal BuildInsuranceValue(OrderItemCollection cbOrderItems)
        {
            decimal value = 0;

            foreach (OrderItem cbOrderItem in cbOrderItems)
            {
                if (cbOrderItem.OrderItemType == OrderItemType.Product && cbOrderItem.Shippable != Shippable.No)
                {
                    switch (Settings.Default.InsuranceValue.Replace(" ", string.Empty).ToUpperInvariant())
                    {
                        case "COSTOFGOODS":
                            value += cbOrderItem.CostOfGoods.ToDecimal(null);
                            break;
                        default:
                            value += cbOrderItem.ExtendedPrice.ToDecimal(null);
                            break;
                    }
                }
            }

            return value;
        }

        private static Item BuildItem(OrderItem cbOrderItem)
        {
            var item = new Item();

            switch (cbOrderItem.OrderItemType)
            {
                case OrderItemType.Coupon:
                    {
                        item = new Item
                        {
                            Code = "Coupon",
                            ItemId = cbOrderItem.OrderItemId.ToString(),
                            LineTotal = cbOrderItem.ExtendedPrice.ToDecimal(null),
                            Name = cbOrderItem.Name,
                            Price = cbOrderItem.Price.ToDecimal(null),
                            Quantity = cbOrderItem.Quantity,
                            Shippable = false
                        };
                    }
                    break;
                case OrderItemType.Discount:
                    {
                        item = new Item
                        {
                            Code = "Discount",
                            ItemId = cbOrderItem.OrderItemId.ToString(),
                            LineTotal = cbOrderItem.ExtendedPrice.ToDecimal(null),
                            Name = cbOrderItem.Name,
                            Price = cbOrderItem.Price.ToDecimal(null),
                            Quantity = cbOrderItem.Quantity,
                            Shippable = false
                        };
                    }
                    break;
                case OrderItemType.GiftCertificate:
                    {
                        item = new Item
                        {
                            Code = "Gift Certificate",
                            ItemId = cbOrderItem.OrderItemId.ToString(),
                            LineTotal = cbOrderItem.ExtendedPrice.ToDecimal(null),
                            Name = cbOrderItem.Name,
                            Price = cbOrderItem.Price.ToDecimal(null),
                            Quantity = cbOrderItem.Quantity,
                            Shippable = false
                        };
                    }
                    break;
                case OrderItemType.GiftWrap:
                    {
                        item = new Item
                        {
                            Code = "Gift Wrap",
                            ItemId = cbOrderItem.OrderItemId.ToString(),
                            LineTotal = cbOrderItem.ExtendedPrice.ToDecimal(null),
                            Name = cbOrderItem.Name,
                            Price = cbOrderItem.Price.ToDecimal(null),
                            Quantity = cbOrderItem.Quantity,
                            Shippable = false
                        };
                    }
                    break;
                case OrderItemType.Product:
                    {
                        item = new Item
                        {
                            Code = cbOrderItem.Sku,
                            ItemId = cbOrderItem.OrderItemId.ToString(),
                            LineTotal = cbOrderItem.ExtendedPrice.ToDecimal(null),
                            Name = BuildItemName(cbOrderItem),
                            Price = cbOrderItem.Price.ToDecimal(null),
                            Properties = BuildItemProperties(cbOrderItem),
                            Quantity = cbOrderItem.Quantity,
                            Weight = cbOrderItem.Quantity * LocaleHelper.ConvertWeight(Token.Instance.Store.WeightUnit, cbOrderItem.Weight, WeightUnit.Pounds).ToDouble(null)
                        };

                        if (cbOrderItem.Product != null)
                        {
                            // OrderItem.Shippable indicates if OrderItem is in a shipment.
                            // Product.Shippable indicates if the item *can* be shipped.
                            item.Shippable = cbOrderItem.Product.Shippable != Shippable.No;

                            if (Token.Instance.Store.EnableInventory && cbOrderItem.Product.InventoryMode != InventoryMode.None)
                                item.Inventory = cbOrderItem.Product.InStock;

                            if (cbOrderItem.Product.Manufacturer != null)
                            {
                                item.Manufacturer = new Supplier();
                                // No manufacturer email in AC
                                item.Manufacturer.Name = cbOrderItem.Product.Manufacturer.Name;
                                item.Manufacturer.SupplierId = cbOrderItem.Product.Manufacturer.ManufacturerId.ToString();
                            }

                            if (cbOrderItem.Product.Vendor != null)
                            {
                                item.Vendor = new Supplier();
                                item.Vendor.Email = cbOrderItem.Product.Vendor.Email;
                                item.Vendor.Name = cbOrderItem.Product.Vendor.Name;
                                item.Vendor.SupplierId = cbOrderItem.Product.VendorId.ToString();
                            }
                        }
                    }
                    break;
            }
            return item;
        }

        private static string BuildItemName(OrderItem cbOrderItem)
        {
            var name = new StringBuilder(cbOrderItem.Name);
            if (!string.IsNullOrEmpty(cbOrderItem.VariantName))
                name.AppendFormat("({0})", cbOrderItem.VariantName);
            return name.ToString();
        }

        private static Property[] BuildItemProperties(OrderItem cbOrderItem)
        {
            var properties = new List<Property>();

            foreach (OrderItemInput input in cbOrderItem.Inputs)
            {
                var property = new Property();
                property.Name = input.Name;
                property.Value = input.InputValue;
                properties.Add(property);
            }
            if (cbOrderItem.WrapStyle != null)
            {
                var property = new Property();
                property.Name = "Gift Wrap";
                property.Value = cbOrderItem.WrapStyle.Name;
                properties.Add(property);
            }
            if (!string.IsNullOrEmpty(cbOrderItem.GiftMessage))
            {
                var property = new Property();
                property.Name = "Gift Wrap Message";
                property.Value = cbOrderItem.GiftMessage;
                properties.Add(property);
            }

            return properties.Count == 0 ? null : properties.ToArray();
        }

        private static string BuildMerchantUrl(string path, string query)
        {
            try
            {
                var uri = new UriBuilder(Token.Instance.Store.StoreUrl);
                if (Token.Instance.Store.Settings.SSLEnabled)
                {
                    uri.Scheme = "https";
                    uri.Port = 443;
                }
                uri.Path += path;
                uri.Query = query;
                return uri.Uri.ToString();
            }
            catch
            {
                return string.Empty;
            }
        }

        private static ItemResult BuildItemResult(OrderItem cbOrderItem)
        {
            var itemResult = new ItemResult();
            try
            {
                itemResult.Item = BuildItem(cbOrderItem);
                itemResult.Success = true;
            }
            catch (Exception ex)
            {
                itemResult.Message = ex.Message;
                itemResult.Success = false;
            }
            return itemResult;
        }

        private static ItemResult[] BuildItemResults(OrderItemCollection cbOrderItems)
        {
            var itemResults = new List<ItemResult>();

            // Build the list in the order that you want the items to appear
            cbOrderItems.Sort("OrderBy");

            foreach (OrderItem cbOrderItem in cbOrderItems)
            {
                switch (cbOrderItem.OrderItemType)
                {
                    case OrderItemType.Coupon:
                    case OrderItemType.Discount:
                    case OrderItemType.GiftCertificate:
                    case OrderItemType.GiftWrap:
                    case OrderItemType.Product:
                        itemResults.Add(BuildItemResult(cbOrderItem));
                        break;
                }
            }

            return itemResults.Count > 0 ? itemResults.ToArray() : null;
        }

        /// <summary>
        /// Build the last updated datetime for the order based on the most
        /// recent system note.
        /// </summary>
        /// <param name="cbOrder">The CommerceBuilder order to inspect.</param>
        /// <returns>The date and time of the last update.</returns>
        private static DateTime BuildOrderLastUpdated(CommerceBuilder.Orders.Order cbOrder)
        {
            var lastUpdated = cbOrder.OrderDate;
            if (cbOrder.Notes == null)
            {
                return lastUpdated;
            }

            foreach (OrderNote note in cbOrder.Notes)
            {
                if (note.IsSystem && note.CreatedDate > lastUpdated)
                {
                    lastUpdated = note.CreatedDate;
                }
            }
            return lastUpdated;
        }

        /// <summary>
        /// Build the database query for GetNewOrders
        /// </summary>
        /// <param name="orderStatusList"></param>
        /// <param name="paymentStatusList"></param>
        /// <param name="pageKey">The page key. Build a query that returns today's orders if pageKey is null.</param>
        /// <param name="getNewAndUpdated">True to retrieve new and updated orders.</param>
        private static DatabaseQuery BuildOrderQuery(string orderStatusList, string paymentStatusList, string pageKey, bool getNewAndUpdated)
        {
            var query = BuildOrderQueryBase(orderStatusList, paymentStatusList, getNewAndUpdated);

            if (getNewAndUpdated)
            {
                query.Sql.Append(" GROUP BY o.OrderId) AS T1");
                if (string.IsNullOrEmpty(pageKey))
                {
                    query.Sql.Append(" WHERE LastUpdated > @Today");
                    query.Parameters.Add(new DatabaseQuery.Parameter("@Today", DbType.Date, DateTime.Today));
                }
                else
                {
                    long binaryDate;
                    if (long.TryParse(pageKey, out binaryDate))
                    {
                        query.Sql.Append(" WHERE LastUpdated > @LastUpdated");
                        query.Parameters.Add(new DatabaseQuery.Parameter("@LastUpdated", DbType.DateTime, DateTime.FromBinary(binaryDate)));
                    }
                    else
                    {
                        throw new ApplicationException(string.Format("Invalid paging key '{0}'.", pageKey));
                    }
                }
            }
            else
            {
                if (string.IsNullOrEmpty(pageKey))
                {
                    query.Sql.Append(" AND o.OrderDate > @Today");
                    query.Parameters.Add(new DatabaseQuery.Parameter("@Today", DbType.Date, DateTime.Today));
                }
                else
                {
                    int startOrderId;
                    if (int.TryParse(pageKey, out startOrderId))
                    {
                        query.Sql.Append(" AND o.OrderId > @StartOrderId");
                        query.Parameters.Add(new DatabaseQuery.Parameter("@StartOrderId", DbType.Int32, startOrderId));
                    }
                    else
                    {
                        throw new ApplicationException(string.Format("Invalid paging key '{0}'.", pageKey));
                    }
                }
                query.Sql.Append(") AS T1");
            }

            return query;
        }

        /// <summary>
        /// Build the database query for GetOrdersByRange
        /// </summary>
        /// <param name="startOrderNumber"></param>
        /// <param name="endOrderNumber"></param>
        /// <param name="startOrderDate"></param>
        /// <param name="endOrderDate"></param>
        /// <param name="orderStatusList"></param>
        /// <param name="paymentStatusList"></param>
        /// <param name="pageKey">The page key. Ignore if null.</param>
        /// <returns></returns>
        private static DatabaseQuery BuildOrderQuery(string startOrderNumber, string endOrderNumber, DateTime? startOrderDate, DateTime? endOrderDate, string orderStatusList, string paymentStatusList, string pageKey)
        {
            var query = BuildOrderQueryBase(orderStatusList, paymentStatusList, false);

            if (!string.IsNullOrEmpty(startOrderNumber))
            {
                int cbOrderNumber;
                if (int.TryParse(startOrderNumber, out cbOrderNumber))
                {
                    query.Sql.Append(" AND o.OrderNumber >= @StartOrderNumber");
                    query.Parameters.Add(new DatabaseQuery.Parameter("@StartOrderNumber", DbType.Int32, cbOrderNumber));
                }
                else
                {
                    throw new ApplicationException(string.Format("Invalid starting order number '{0}'.", startOrderNumber));
                }
            }
            if (!string.IsNullOrEmpty(endOrderNumber))
            {
                int cbOrderNumber;
                if (int.TryParse(endOrderNumber, out cbOrderNumber))
                {
                    query.Sql.Append(" AND o.OrderNumber <= @EndOrderNumber");
                    query.Parameters.Add(new DatabaseQuery.Parameter("@EndOrderNumber", DbType.Int32, cbOrderNumber));
                }
                else
                {
                    throw new ApplicationException(string.Format("Invalid ending order number '{0}'.", endOrderNumber));
                }
            }
            if (startOrderDate.HasValue)
            {
                query.Sql.Append(" AND o.OrderDate >= @StartOrderDate");
                query.Parameters.Add(new DatabaseQuery.Parameter("@StartOrderDate", DbType.DateTime, startOrderDate.Value));
            }
            if (endOrderDate.HasValue)
            {
                query.Sql.Append(" AND o.OrderDate <= @EndOrderDate");
                query.Parameters.Add(new DatabaseQuery.Parameter("@EndOrderDate", DbType.DateTime, endOrderDate.Value));
            }
            
            // Support paging
            if (!string.IsNullOrEmpty(pageKey))
            {
                int startOrderId;
                if (int.TryParse(pageKey, out startOrderId))
                {
                    query.Sql.Append(" AND o.OrderId > @StartOrderId");
                    query.Parameters.Add(new DatabaseQuery.Parameter("@StartOrderId", DbType.Int32, startOrderId));
                }
                else
                {
                    throw new ApplicationException(string.Format("Invalid paging key '{0}'.", pageKey));
                }
            }

            query.Sql.Append(") AS T1");
            return query;
        }

        private static DatabaseQuery BuildOrderQueryBase(string orderStatusList, string paymentStatusList, bool getNewAndUpdated)
        {
            var query = new DatabaseQuery();

            if (getNewAndUpdated)
            {
                query.Sql.Append("(SELECT o.OrderId, MAX(n.CreatedDate) AS LastUpdated FROM ac_Orders o INNER JOIN ac_OrderNotes n ON o.OrderId = n.OrderId WHERE n.NoteTypeId IN (2,3)");
            }
            else
            {
                query.Sql.Append("(SELECT o.OrderId, o.OrderDate AS LastUpdated FROM ac_Orders o WHERE 1=1");
            }

            if (!string.IsNullOrEmpty(orderStatusList))
            {
                query.Sql.AppendFormat(" AND o.OrderStatusId IN ({0})", orderStatusList);
            }

            if (!string.IsNullOrEmpty(paymentStatusList))
            {
                query.Sql.AppendFormat(" AND o.PaymentStatusId IN ({0})", paymentStatusList);
            }

            return query;
        }

        private static OrderResult BuildOrderResult(CommerceBuilder.Orders.Order cbOrder)
        {
            var order = new Order();
            var orderResult = new OrderResult {Order = order};

            // Build the "simple" fields that have little chance of failing
            order.CustomerUrl = BuildCustomerUrl("Members/MyOrder.aspx", "OrderId=" + cbOrder.OrderId);
            order.MerchantUrl = BuildMerchantUrl("Admin/Orders/ViewOrder.aspx", "OrderId=" + cbOrder.OrderId);
            order.OrderDate = cbOrder.OrderDate;
            order.OrderId = cbOrder.OrderId.ToString();
            order.OrderNumber = cbOrder.OrderNumber.ToString();
            order.OrderStatus = cbOrder.OrderStatus.DisplayName;
            order.OrderTotal = cbOrder.TotalCharges.ToDecimal(null);
            order.PaymentStatus = ShippingAgentHelper.SplitCompoundName(cbOrder.PaymentStatus.ToString());
            order.ShippingStatus = ShippingAgentHelper.SplitCompoundName(cbOrder.ShipmentStatus.ToString());

            try
            {
                order.BillingAddress = BuildAddress(cbOrder);
                order.Charges = BuildCharges(cbOrder.Items);
                order.Customer = BuildCustomer(cbOrder.User);
                order.Items = BuildItemResults(cbOrder.Items);
                order.LastUpdated = BuildOrderLastUpdated(cbOrder);
                order.Packages = BuildPackageRefs(cbOrder);
                order.Payments = BuildPayments(cbOrder);

                orderResult.Success = true;
                return orderResult;
            }
            catch (ApplicationException ex)
            {
                orderResult.Success = false;
                orderResult.Message = ex.Message;
                return orderResult;
            }
            catch (Exception ex)
            {
                orderResult.Success = false;
                orderResult.Message = ex.ToString();
                return orderResult;
            }
        }

        private static PackageRef[] BuildPackageRefs(CommerceBuilder.Orders.Order cbOrder)
        {
            var packageRefs = new List<PackageRef>();
            foreach (OrderShipment cbOrderShipment in cbOrder.Shipments)
            {
                if (cbOrderShipment.TrackingNumbers == null || cbOrderShipment.TrackingNumbers.Count == 0)
                {
                    var packageRef = new PackageRef();
                    packageRef.OrderId = cbOrderShipment.OrderId.ToString();
                    packageRef.PackageId = BuildPackageId(cbOrderShipment, null);
                    packageRefs.Add(packageRef);
                }
                else
                {
                    foreach (TrackingNumber cbTrackingNumber in cbOrderShipment.TrackingNumbers)
                    {
                        var packageRef = new PackageRef();
                        packageRef.OrderId = cbOrderShipment.OrderId.ToString();
                        packageRef.PackageId = BuildPackageId(cbOrderShipment, cbTrackingNumber);
                        packageRefs.Add(packageRef);
                    }
                }
            }
            return packageRefs.Count > 0 ? packageRefs.ToArray() : null;
        }

        private static PackageResult BuildPackageResult(OrderShipment cbOrderShipment, TrackingNumber cbTrackingNumber)
        {
            var package = new Package();
            var result = new PackageResult {Package = package};

            // Build the "simple" fields that have little chance of failing
            package.CustomerUrl = BuildCustomerUrl("Members/MyOrder.aspx", "OrderId=" + cbOrderShipment.OrderId);
            package.MerchantUrl = BuildMerchantUrl("Admin/Orders/Shipments/Default.aspx", "OrderId=" + cbOrderShipment.OrderId);
            package.OrderId = cbOrderShipment.OrderId.ToString();
            package.PackageId = cbOrderShipment.OrderShipmentId + (cbTrackingNumber == null ? string.Empty : "," + cbTrackingNumber.TrackingNumberId);

            try
            {
                // Add a tracking number if there isn't one yet assigned.
                if (cbTrackingNumber == null)
                {
                    if (cbOrderShipment.TrackingNumbers == null || cbOrderShipment.TrackingNumbers.Count == 0)
                    {
                        cbTrackingNumber = new TrackingNumber();
                        cbTrackingNumber.OrderShipmentId = cbOrderShipment.OrderShipmentId;
                        if (cbOrderShipment.ShipMethod.ShipGateway != null)
                        {
                            cbTrackingNumber.ShipGatewayId = cbOrderShipment.ShipMethod.ShipGatewayId;
                        }
                        if (cbTrackingNumber.Save() == SaveResult.Failed)
                        {
                            throw new ApplicationException("Cannot add tracking number to order shipment.");
                        }
                    }
                    else
                    {
                        cbTrackingNumber = cbOrderShipment.TrackingNumbers[0];
                    }
                    package.PackageId = cbOrderShipment.OrderShipmentId + "," + cbTrackingNumber.TrackingNumberId;
                }
                package.Charges = BuildCharges(cbOrderShipment.OrderItems);
                package.Destination = BuildShippingAddress(cbOrderShipment);
                package.Instructions = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipMessage);
                package.InsuranceValue = BuildInsuranceValue(cbOrderShipment.OrderItems);
                package.Items = BuildItemResults(cbOrderShipment.OrderItems);
                package.RateOptionName = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipMethodName);
                package.RateProviderName = BuildRateProviderName(cbOrderShipment.ShipMethod);
                if (cbOrderShipment.IsShipped)
                {
                    package.ShipDate = cbOrderShipment.ShipDate;
                    package.ShipDateSpecified = true;
                }
                else
                {
                    package.ShipDateSpecified = false;
                }
                package.Shipped = cbOrderShipment.IsShipped;
                package.TrackingNumber = cbTrackingNumber.TrackingNumberData;
                if (cbTrackingNumber.ShipGateway != null)
                {
                    package.TrackingProviderId = cbTrackingNumber.ShipGatewayId.ToString();
                    package.TrackingProviderName = cbTrackingNumber.ShipGateway.Name;
                }
                package.Warehouse = BuildAddress(cbOrderShipment.Warehouse);
                package.Weight = BuildWeight(cbOrderShipment.OrderItems);

                result.Success = true;
                return result;
            }
            catch (ApplicationException ex)
            {
                result.Success = false;
                result.Message = ex.Message;
                return result;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.ToString();
                return result;
            }
        }

        private static string BuildPackageId(OrderShipment cbOrderShipment, TrackingNumber cbTrackingNumber)
        {
            return cbOrderShipment.OrderShipmentId + (cbTrackingNumber == null ? string.Empty : "," + cbTrackingNumber.TrackingNumberId);
        }

        /// <summary>
        /// Build a list of payments from a CommerceBuilder order.
        /// </summary>
        /// <param name="cbOrder">The CommerceBuilder order.</param>
        /// <returns>The list of payments represented as NamedAmounts.</returns>
        /// <remarks>CommerceBuilder treats Gift Certificate redemption as a payment.</remarks>
        private static NamedAmount[] BuildPayments(CommerceBuilder.Orders.Order cbOrder)
        {
            var payments = new NamedAmountList();

            foreach (Payment cbPayment in cbOrder.Payments)
            {
                // Shipper does not have payment processing features.
                // Only send completed and captured payments.
                if (cbPayment.PaymentStatus != CommerceBuilder.Payments.PaymentStatus.Completed
                    && cbPayment.PaymentStatus != CommerceBuilder.Payments.PaymentStatus.Captured) 
                    continue;

                payments[cbPayment.PaymentMethodName, cbPayment.ReferenceNumber].Amount 
                    += cbPayment.Amount.ToDecimal(null);
            }

            return payments.Count > 0 ? payments.ToArray() : null;
        }

        private static string BuildRateProviderName(ShipMethod shipMethod)
        {
            if (shipMethod.ShipGateway == null)
                switch (shipMethod.ShipMethodType)
                {
                    case ShipMethodType.FlatRate:
                        return "Flat Rate";
                    case ShipMethodType.WeightBased:
                        return "Vary by Weight";
                    case ShipMethodType.CostBased:
                        return "Vary by Cost";
                    case ShipMethodType.QuantityBased:
                        return "Vary by Quantity";
                    default:
                        return string.Empty;
                }
            return shipMethod.ShipGateway.Name;
        }

        private static ShippingAddress BuildShippingAddress(OrderShipment cbOrderShipment)
        {
            var address = new ShippingAddress();
            address.AddressId = cbOrderShipment.OrderShipmentId.ToString();
            address.City = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToCity);
            address.Company = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToCompany);
            address.Country = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToCountryCode);
            address.Email = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToEmail);
            address.Fax = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToFax);
            address.FirstName = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToFirstName);
            address.LastName = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToLastName);
            address.Line1 = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToAddress1);
            address.Line2 = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToAddress2);
            address.Phone = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToPhone);
            address.PostalCode = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToPostalCode);
            address.Region = ShippingAgentHelper.StringOrNull(cbOrderShipment.ShipToProvince);
            address.Residential = cbOrderShipment.ShipToResidence;
            return address;
        }

        private static UpdateResult BuildUpdateResult(Update update, OrderShipment cbOrderShipment, TrackingNumber cbTrackingNumber)
        {
            var notes = new List<string>();

            if (cbTrackingNumber == null)
            {
                cbTrackingNumber = new TrackingNumber();
                cbTrackingNumber.OrderShipmentId = cbOrderShipment.OrderShipmentId;
                if (cbOrderShipment.ShipMethod.ShipGateway != null)
                {
                    cbTrackingNumber.ShipGatewayId = cbOrderShipment.ShipMethod.ShipGatewayId;
                }
                if (cbTrackingNumber.Save() == SaveResult.Failed)
                {
                    return new UpdateResult { Success = false, Message = "Cannot add tracking number to order shipment." };
                }
            }

            if (update.Destination != null && !AddressesAreEqual(cbOrderShipment, update.Destination))
            {
                cbOrderShipment.ShipToAddress1 = update.Destination.Line1;
                cbOrderShipment.ShipToAddress2 = update.Destination.Line2;
                cbOrderShipment.ShipToCity = update.Destination.City;
                cbOrderShipment.ShipToCompany = update.Destination.Company;
                cbOrderShipment.ShipToCountryCode = update.Destination.Country;
                cbOrderShipment.ShipToEmail = update.Destination.Email;
                cbOrderShipment.ShipToFax = update.Destination.Fax;
                cbOrderShipment.ShipToFirstName = update.Destination.FirstName;
                cbOrderShipment.ShipToLastName = update.Destination.LastName;
                cbOrderShipment.ShipToPhone = update.Destination.Phone;
                cbOrderShipment.ShipToPostalCode = update.Destination.PostalCode;
                cbOrderShipment.ShipToProvince = update.Destination.Region;
                cbOrderShipment.ShipToResidence = update.Destination.Residential;
                if (cbOrderShipment.Save() != SaveResult.Failed)
                    notes.Add(string.Format("Updated shipping address for shipment #{0}.", cbOrderShipment.ShipmentNumber));
                else
                    return new UpdateResult { Success = false, Message = "Cannot save order shipping address." };
            }

            if (!string.IsNullOrEmpty(update.TrackingNumber))
            {
                int providerId;
                if (!string.IsNullOrEmpty(update.TrackingProviderId) && int.TryParse(update.TrackingProviderId, out providerId))
                    cbTrackingNumber.ShipGatewayId = providerId;
                cbTrackingNumber.TrackingNumberData = update.TrackingNumber;
                if (cbTrackingNumber.Save() != SaveResult.Failed)
                    notes.Add(string.Format("Added tracking number {0} to shipment #{1}.", update.TrackingNumber, cbOrderShipment.ShipmentNumber));
                else
                    return new UpdateResult { Success = false, Message = "Cannot save tracking number." };
            }

            if (update.ShipDateSpecified)
            {
                if (cbOrderShipment.ShipDate != update.ShipDate)
                {
                    cbOrderShipment.Ship(update.ShipDate, !string.IsNullOrEmpty(cbOrderShipment.Order.GoogleOrderNumber));
                    notes.Add(string.Format("Shipped shipment #{0}.", cbOrderShipment.ShipmentNumber));
                }
            }

            if (update.ShippingCostSpecified)
            {
                notes.Add(string.Format("Actual shipping cost is {0:c}.", update.ShippingCost));
            }

            if (notes.Count > 0)
            {
                var message = string.Join(" ", notes.ToArray());
                var text = string.Format(ShippingAgentHelper.ORDER_UPDATE_TEXT, message);
                cbOrderShipment.Order.Notes.Add(new OrderNote(cbOrderShipment.OrderId, 0, DateTime.UtcNow, text, NoteType.SystemPrivate));
                cbOrderShipment.Order.Notes.Save();
                var cbOrder = OrderDataSource.Load(cbOrderShipment.OrderId, false);
                var result = new UpdateResult();
                result.Message = message;
                result.OrderResult = BuildOrderResult(cbOrder);
                result.PackageResult = BuildPackageResult(cbOrderShipment, cbTrackingNumber);
                result.Success = true;
                return result;
            }

            return new UpdateResult { Success = true, Message = "No change." };
        }

        private static double BuildWeight(OrderItemCollection cbOrderItems)
        {
            double weight = 0;

            foreach (OrderItem cbOrderItem in cbOrderItems)
            {
                if (cbOrderItem.OrderItemType == OrderItemType.Product && cbOrderItem.Shippable != Shippable.No)
                    weight += cbOrderItem.Quantity * LocaleHelper.ConvertWeight(Token.Instance.Store.WeightUnit, cbOrderItem.Weight, WeightUnit.Pounds).ToDouble(null);
            }

            return weight;
        }

        /// <summary>
        /// Simple class used to encapsulate custom database queries.
        /// </summary>
        protected class DatabaseQuery
        {
            public readonly StringBuilder Sql = new StringBuilder();
            public readonly ICollection<Parameter> Parameters = new List<Parameter>();

            public class Parameter
            {
                public readonly string Name;
                public readonly DbType DbType;
                public readonly object Value;

                public Parameter(string name, DbType dbType, object value)
                {
                    Name = name;
                    DbType = dbType;
                    Value = value;
                }
            }
        }
    }
}